package com.base.cn.platform.os.common.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * @Author zjh
 * @Description Excel
 * @Date 2018/9/11 16:08
 */
public class FileExportImportUtil {

    private static final Logger logger = LoggerFactory.getLogger(FileExportImportUtil.class);

    /**
     * 导出excel
     *
     * @param titles   表格头内容
     * @param data     表格体内容
     * @param filename 导出文件名
     * @return
     */
    public static ResponseEntity<byte[]> exportExcel(String[] titles, List<List<String>> data, String filename) throws IOException {
        return getHSSFWorkbook(titles, data, filename,"xls");
    }

    private static ResponseEntity<byte[]> getHSSFWorkbook(String[] titles, List<List<String>> data, String filename, String extension) throws IOException {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        Workbook workbook = getWorkbook(extension);
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = workbook.createSheet();
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        Row row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        CellStyle cellStyle = getCellStyle(workbook);
        //第五部设置表头
        setTitles(row,titles,cellStyle);
        //创建行内容
        setRows(data,row,sheet,cellStyle);
        try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
            filename = new String((filename + "." + extension).getBytes("utf-8"), "iso8859-1");
            HttpHeaders headerst = new HttpHeaders();
            headerst.setContentDispositionFormData("attachment", filename);
            headerst.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);
            return new ResponseEntity<byte[]>(baos.toByteArray(), headerst, HttpStatus.OK);
        } catch (IOException ioe) {
            logger.error("exportExcel()-error", ioe);
        }
        return null;
    }

    /**
     *
     * @param titles 头部
     * @param data
     * @return
     * @throws Exception
     */
    public static ByteArrayInputStream createExcel(String[] titles, List<List<String>> data) throws Exception {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        Workbook workbook = new HSSFWorkbook();
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = workbook.createSheet();
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        Row row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        CellStyle cellStyle = getCellStyle(workbook);
        //第五部设置表头
        setTitles(row,titles,cellStyle);
        //声明列对象
        HSSFCell cell = null;
        //创建行内容
        setRows(data,row,sheet,cellStyle);
        //workbook 转化成流文件
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        workbook.write(os);
        byte[] bytes = os.toByteArray();
        return new ByteArrayInputStream(bytes);
    }

    /**
     * 生成文件名
     * @param fileName
     * @return
     */
    private static String getFileName(String fileName){
        return  fileName+".xls";
    }

    /**
     * 设置表格样式
     * @param workbook
     * @return
     */
    private static CellStyle  getCellStyle(Workbook workbook){
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("宋体");
        cellStyle.setFont(font);
        return  cellStyle;
    }

    /**
     * 设置表头
     * @param row
     * @param titles
     * @param cellStyle
     */
    private static void setTitles(Row row,String [] titles,CellStyle cellStyle){
        Cell cell = null;
        //添加‘序号’标题
        cell = row.createCell(0);
        cell.setCellValue("序号");
        //创建标题
        for(int i=0;i<titles.length;i++){
            cell = row.createCell(i+1);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(cellStyle);
        }
    }

    /**
     * 设置行内容
     * @param data
     * @param row
     * @param sheet
     * @param cellStyle
     */
    private static void setRows(List<List<String>> data,Row row,Sheet sheet,CellStyle cellStyle){
       Cell cell = null;
        if (ObjectUtils.isNotEmpty(data)) {
            List<String> list;
            int size = row == null ? data.size() : data.size() + 1;
            int length = 0;
            for (int i = row == null ? 0 : 1; i < size; i++) {
                row = sheet.createRow(i);
                list = data.get(i - 1);
                length = list.size();
                //创建第一行序号
                if(i!=0){
                    cell = row.createCell(0);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(i);
                }
                for (int j = 0; j < length; j++) {
                    cell = row.createCell(j+1);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(list.get(j));
                }
            }
            for (int i = 0; i <= length; i++) {
                sheet.autoSizeColumn(i, true);
            }
        }
    }

    /**
     * 工作簿
     *
     * @param extension 扩展名
     */
    private static Workbook getWorkbook(String extension) {
        return "xlsx".equalsIgnoreCase(extension) ? new XSSFWorkbook() : new HSSFWorkbook();
    }

}
